<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use app\index\controller\Login;
use think\Db;
use app\index\service\ExcelService;

class ArchiveBookTable extends BaseTable
{
    protected static $table = 'ARCHIVE_BOOK';

    static public function table(){
        $sql = "(
            select 
            b.ARCHIVE_YEARMONTH
            ,b.book_no
            ,to_char(b.input_date,'yyyy-mm-dd') input_date
            ,b.check_land_no
            ,b.asset_type_id
            ,d1.cname94 ASSET_TYPE
            ,b.book_name
            ,b.book_unit
            ,b.book_num
            ,b.ANTIQUERANK_ID
            ,b.SAVE_ROOM
            ,b.SAVE_AREA
            ,b.VALUETYPE_ID
            ,d2.VALUETYPE
            ,b.BOOK_FEE
            ,b.BOOK_VIRTUAL_FEE
            ,b.FINANCE_GET_FEE
            ,b.BUSINESS_GET_FEE
            ,b.BUSINESS_BUDGET_GET_FEE
            ,b.OTHER_FEE
            ,b.OTHER_FINANCE_FEE
            ,b.ACCOUNT_CERT_NO
            ,b.PURCHASE_ID
            ,d3.PURCHASE
            ,b.STATUS_ID
            ,d6.STATUS
            ,b.USEDIRECTION_ID
            ,d5.USEDIRECTION
            ,to_char(b.GET_DATE,'yyyy-mm-dd') GET_DATE
            ,b.GETTYPE_ID
            ,d4.GETTYPE
            ,b.PROPERTYRIGHT_ID
            ,b.COLLEGE_ID
            ,d7.COLLEGE USE_COLLEGE
            ,b.MGR_COLLEGE_ID
            ,d10.COLLEGE MGR_COLLEGE
            ,b.USER_MGR_ID
            ,d9.NAME MGR_USER_NAME
            ,b.USE_NAME
            ,d8.NAME USER_NAME
            ,b.REMARK
            ,b.USER_ID
            ,to_char(b.OPER_DATETIME,'yyyy-mm-dd') OPER_DATETIME
            from app_asset.archive_book b
            left join app_asset.ASSET_DICT_TYPE94TO2010 d1 on d1.ccode94=b.asset_type_id
            left join app_asset.ASSET_DICT_VALUETYPE d2 on d2.valuetype_id=b.VALUETYPE_ID
            left join app_asset.ASSET_DICT_PURCHASE d3 on d3.PURCHASE_ID=b.PURCHASE_ID
            left join app_asset.ASSET_DICT_GETTYPE d4 on d4.GETTYPE_ID=b.GETTYPE_ID
            left join app_asset.ASSET_DICT_USEDIRECTION d5 on d5.USEDIRECTION_ID=b.USEDIRECTION_ID
            left join app_asset.ASSET_DICT_STATUS d6 on d6.STATUS_ID=b.STATUS_ID
            left join app_asset.DICT_COLLEGE d7 on d7.COLLEGE_ID=b.COLLEGE_ID
            left join app_asset.EMPLOYEE d8 on d8.STAFF_ID=b.USE_NAME
            left join app_asset.EMPLOYEE d9 on d9.STAFF_ID=b.USER_MGR_ID
            left join app_asset.DICT_COLLEGE d10 on d10.COLLEGE_ID=b.MGR_COLLEGE_ID
        )";
        return Db::table($sql);
    }

    static public function insert($data){
        $yearmonth = $data['ARCHIVE_YEARMONTH'];
        $bookNo = $data['BOOK_NO'];
        self::toDate($data,'GET_DATE');
        self::toDate($data,'REPAIR_DEADLINE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'PRESS_DATE');
        self::toDate($data,'INPUT_TIME');

        $old = Db::table(static::$table)->where(['ARCHIVE_YEARMONTH'=>$yearmonth,'BOOK_NO'=>$bookNo])->find();
        if(empty($old)){
            return Db::table(static::$table)->insert($data);
        }
        else{
            return Db::table(static::$table)
                ->where(['ARCHIVE_YEARMONTH'=>$yearmonth,'BOOK_NO'=>$bookNo])
                ->update($data);
        }
    }

}